﻿Public Class frmTablesSearch
    Public pTable As String
    Public pTitre As String
    Public pCode As String
    Public pLibelle As String

    Private szSQL As String
    Private rs As ADODB.Recordset
    Private m_SortingColumn As ColumnHeader

    Private Sub frmTablesSearch_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        InitListeCodes()
        Me.Text = pTitre
        pCode = ""
        pLibelle = ""
    End Sub

    Private Sub InitListeCodes()
        Dim nb As Integer = 0
        Dim ix As Integer = 0

        ' effacer la listview
        LV_CODES.Items.Clear()

        ' Lire la table 
        Select Case pTable
            Case "moniteurs"
                szSQL = "CALL spGet_MONITEURS('')"

            Case "moniteurs_aff"
                ' Lire la table AFFECTATIONS MONITEURS
                szSQL = "CALL spGet_MONITEURS_AFF_byAGENCE(" & gUserAgence & ",'" & Format(Now, "yyyy/MM/dd") & "','" & Format(Now, "yyyy/MM/dd") & "','ALL')"

            Case "p_inspecteurs"
                szSQL = "SELECT * FROM p_inspecteurs ORDER BY libelle"

            Case "p_libelle_regl"
                szSQL = "SELECT * FROM p_libelle_regl ORDER BY libelle"

            Case "formules"
                ' lire les formules selon le permis demande et l'option "valide" oui ou non
                szSQL = "CALL spGet_FORMULES_valide('" & pLibelle & "','" & pCode & "')"

            Case Else
                MsgBox(pTable & " : Table de recherche inconnue")
                Exit Sub

        End Select
        GetRecordset(ctDSN_AEM, szSQL, rs)

        ' Load the data.
        Do While Not rs.EOF
            Select Case pTable
                Case "moniteurs"
                    LV_CODES.Items.Add(rs.Fields("nom").Value)
                    LV_CODES.Items(nb).SubItems.Add(nv(rs.Fields("cd_mon").Value))
                    nb = nb + 1
                Case "moniteurs_aff"
                    LV_CODES.Items.Add(rs.Fields("intitule").Value)
                    LV_CODES.Items(nb).SubItems.Add(nv(rs.Fields("cd_mon").Value))
                    nb = nb + 1
                Case "formules"
                    ' n'affiche de les formules valides
                    If DateDiff(DateInterval.Day, rs.Fields("DateDeb").Value, Now) >= 0 And _
                        DateDiff(DateInterval.Day, rs.Fields("DateFin").Value, Now) <= 0 Then
                        LV_CODES.Items.Add(rs.Fields("libelle").Value)
                        LV_CODES.Items(nb).SubItems.Add(nv(rs.Fields("formule").Value))
                        nb = nb + 1
                    End If
                Case Else  ' tables "p_inspecteurs", "p_libelle_regl"
                    LV_CODES.Items.Add(rs.Fields("libelle").Value)
                    LV_CODES.Items(nb).SubItems.Add(nv(rs.Fields("code").Value))
                    nb = nb + 1

            End Select
            rs.MoveNext()
        Loop
        ' Close the recordset AND connection.
        rs.Close()
        If pTable = "moniteurs_aff" Then
            LV_CODES.Items.Add("DECHARGE")
            LV_CODES.Items(nb).SubItems.Add("ZZ")
            nb = nb + 1
        End If
        ' sélectionner sur le code de l'élément du combo
        If nb > 0 Then
            Dim findItem As ListViewItem = LV_CODES.FindItemWithText(pCode, False, 0, True)
            If findItem IsNot Nothing Then
                LV_CODES.TopItem = findItem
                findItem.Selected = True
            End If
        End If

    End Sub

    Private Sub LV_CODES_ColumnClick(ByVal sender As Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles LV_CODES.ColumnClick
        SortListView(LV_CODES, m_SortingColumn, e.Column)
    End Sub

    Private Sub LV_CODES_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles LV_CODES.DoubleClick

        If LV_CODES.SelectedItems.Count > 0 Then
            ' save code sélectionné
            pLibelle = LV_CODES.SelectedItems(0).Text
            pCode = LV_CODES.SelectedItems(0).SubItems(1).Text
            Me.Close()
        End If

    End Sub

    Private Sub LV_CODES_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles LV_CODES.KeyDown

        Select Case e.KeyCode
            ' touche Enter = DoubleClick
            Case Keys.Enter
                LV_CODES_DoubleClick(sender, e)

        End Select

    End Sub
End Class